--
USE "xsgl";
--一：对给定的xsgl数据库完成以下查询要求 ：对xs表增加身份证号码属性列，要求是18位的字符类型
go

alter table xs
    add 身份证 char(18)
go

--1、查询有直接先行课的课程的课号，课名和先行课号。

select kc.课程号,kc.课程名,kc.先行课号
from kc
where kc.先行课号 is not NULL

--2、查询先行课号是“J001”号课程的课号和课名

select kc.课程号,kc.课程名
from kc
where kc.先行课号 = 'J001'

--3、查询所有的网络工程系姓李，张，王的同学的学号和姓名

select xs.学号,xs.姓名
from xs
where 姓名 like '[李,张,王]%'
  and xs.专业 = '网络工程'

--4、查询不在网络工程和信息管理专业学习的学生的学号和姓名，系别，并对查询结果按照专业的升序和学号的降序排序

select xs.学号,xs.姓名,xs.专业
from xs
where xs.专业 not in('网络工程','信息管理')
order by xs.专业 asc,xs.学号 desc

--5、查询每门课不及格的学生的人数，显示课号和人数

select cj.课程号,人数 = count(*)
from cj
where cj.成绩 < 60
group by cj.课程号

--6、查询年龄不在30-35之间的网络工程系的学生的学号，姓名和年龄

select xs.学号,xs.姓名,年龄 = datediff(year,xs.出生时间,getdate())
from xs
where datediff(year,xs.出生时间,getdate()) < 30 or datediff(year,xs.出生时间,getdate()) >35 and xs.专业='网络工程'

--7、查询没有选修‘J001’号课程的学生的学号（注意去掉重复的元组）

select distinct xs.学号
from xs left join (select cj.课程号,cj.学号 from cj where cj.课程号 = 'J001' ) as tmp on xs.学号 = tmp.学号
where tmp.课程号 is null

--8、查询每个学生的学号，姓名，出生年份，并给出生年份起别名为chusheng

select xs.学号,xs.姓名,chusheng = Year(xs.出生时间)
from xs

--9、查询每个学生的学号，姓名和出生日期（出生日期根据身份证号码查询）

select xs.学号,xs.姓名,出生日期 = convert(date,substring(xs.身份证,7,8),111)
from xs

--10、查询选修J001课程成绩排名第一的同学的学号和成绩

select top 1 cj.学号,cj.成绩
from cj
where cj.课程号 = 'J001'
order by cj.成绩 desc

--11、查询所有名字中含有’明’或者’丽’的同学的学号，姓名

select xs.学号,xs.姓名
from xs
where xs.姓名 like '%[明，丽]%'

--12、查询信息管理专业年龄超过20岁的学生的人数

select 信息管理专业年龄超过20岁的学生的人数 = count(*)
from xs
where datediff(year,xs.出生时间,getdate()) > 20
group by xs.专业
having xs.专业 = '信息管理'

--13、查询平均成绩超过80分的课程的课程号和平均成绩

select cj.课程号,平均成绩 = avg(成绩)
from cj
group by 课程号
having avg(成绩) > 80

--14、查询每个专业所有姓张的人数

select xs.专业,count(*)
from xs
where xs.姓名 like '张%'
group by xs.专业

--15、查询各种姓氏的人数（假设没有复姓）

select 姓氏 = left(xs.姓名,1),count(*)
from xs
group by left(xs.姓名,1)

--16、查询选修课程超过5门的学生的学号和选课门数，以及平均成绩

select cj.学号,选课门数 = count(cj.课程号),平均成绩 = avg(cj.成绩)
from cj
group by cj.学号
having count(cj.课程号) > 5

--17、查询选修‘J001’课程的成绩排名前五的学生的学号和成绩

select top 5 cj.学号,cj.成绩
from cj
where cj.课程号 = 'J001'
order by cj.成绩 desc

--18、查询每个学生的最低分和选课门数

select xs.学号,xs.姓名,最低分 = min(cj.成绩),选课数 = count(cj.课程号)
from xs left join cj on xs.学号 = cj.学号
group by xs.学号,xs.姓名

--19、查询各个专业各种性别的人数

select xs.专业,xs.性别,人数 = count(*)
from xs
group by xs.专业,xs.性别

--20、查询各个专业男生的人数

select xs.专业,count(*)
from xs
where xs.性别 = '男'
group by xs.专业

--21、列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select cj.学号,平均成绩 = avg(成绩)
from cj,(select cj.学号
         from cj
         where cj.成绩 < 60
         group by cj.学号
         having count(cj.课程号) > = 2) as tmp
where cj.学号 = tmp.学号
group by cj.学号

--22、显示学号第五位或者第六位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及专业

select xs.学号,xs.姓名,xs.性别,年龄 = datediff(year,xs.出生时间,getdate()),xs.专业
from xs
where substring(xs.学号,5,2) like '%[1,2,3,4,9]%'

--23、显示选修课程数最多的学号及选修课程数最少的学号

select cj.学号,选修课程数 = count(cj.课程号)
from cj
group by cj.学号
having count(cj.课程号) > = all(select count(cj.课程号)
                             from cj group by cj.学号) or  count(cj.课程号) < = all(select count(cj.课程号)
                                                                               from cj group by cj.学号)

--24、查询选修了A001或者A002或者J001或者J002课程的学生的学号和课程号

select cj.学号,cj.课程号
from cj
where cj.课程号 in('A001','A002','J001','J002')

--25、查询姓名为两个字的不同姓氏的人数，输出姓氏，人数

select 姓氏 = left(xs.姓名,1),人数 = count(*)
from xs
where len(xs.姓名) = 2
group by left(xs.姓名,1)

--26、查询选修了A001或者A002或者J001或者J002课程的学生的课程号，课程名和选课人数

select cj.课程号,kc.课程名,选课人数 = count(*)
from cj,kc
where cj.课程号 = kc.课程号 and
        cj.课程号 in('A001','A002','J001','J002')
group by cj.课程号,kc.课程名

--27、查询每个学生的学号，姓名以及成绩及格的课程门数！

select xs.学号,xs.姓名,及格课程数 = count(case when cj.成绩 >= 60 then 1 end )
from xs left join cj on xs.学号 = cj.学号
group by xs.学号,xs.姓名

--28、查询每个学生的学号，姓名以及选课门数和平均成绩

select xs.学号,xs.姓名,选课门数 = count(cj.课程号),平均成绩 = avg(cj.成绩)
from xs left join cj on xs.学号 = cj.学号
group by xs.学号,xs.姓名

--29、查询选修选修数据结构和操作系统的课程名和选课人数

select kc.课程名,选课人数 = count(*)
from kc,cj
where kc.课程名 in('数据结构','操作系统') and kc.课程号 = cj.课程号
group by kc.课程名

--30、查询选课人数超过五人的课程号和课程名以及该课程的最高分

select kc.课程号,kc.课程名,最高分 = max(cj.成绩)
from kc,cj
where kc.课程号 = cj.课程号
group by kc.课程号,kc.课程名
having count(*) > 5

--
USE "SPJ";

--1、求供应工程J1零件的供应商号码SNO
select distinct spj.sno
from spj
where jno = 'J1'

--2、查询每个工程使用不同供应商的零件的个数
select j.jno,s.sno,使用不同供应商的零件的个数 = isnull(sum(spj.qty),0)
from (j cross join s )left join spj on s.sno = spj.sno and j.jno = spj.jno
group by j.jno,s.sno
order by j.jno asc

--3、求供应工程使用零件P3数量超过200的工程号JNO !
select 使用零件P3数量超过200的工程号JNO = spj.jno
from spj
where spj.pno = 'P3'
group by spj.jno
having sum(spj.qty) > 200

--4、求颜色为红色和蓝色的零件的零件号和名称
select p.pno,p.pname
from p
where p.color in('红','蓝')

--5、求使用零件数量在200-400之间的工程号
select "零件数量在200-400之间的工程号JNO" = spj.jno
from spj
group by spj.jno
having sum(spj.qty) >= 200 and sum(spj.qty) <= 400

--6、查询每种零件的零件号，以及使用该零件的工程数
select p.pno,使用该零件的工程数 = count(spj.pno)
from p left join spj on p.pno = spj.pno
group by p.pno

--7、查询每个工程所使用的不同供应商供应的零件数量，输出工程号，供应商号和零件数量
select j.jno,spj.sno,零件数量 = sum(spj.qty)
from j left join spj on j.jno = spj.jno
group by j.jno,spj.sno

--8、查询没有供应任何零件的供应商号，供应商名称
select s.sno,s.sname
from s left join spj on s.sno = spj.sno
where spj.pno is null

--9、查询使用了S1供应商供应的红色零件的工程号和工程名
select spj.jno,j.jname
from spj,j,p
where spj.jno = j.jno and spj.pno = p.pno
  and spj.sno = 's1'
  and p.color = '红'

--10、查询各个工程的工程号，工程名以及所使用的的零件数量
select j.jno,j.jname,零件数量 = isnull(sum(spj.qty),0)
from j left join spj on j.jno = spj.jno
group by j.jno,j.jname
order by jno asc
--三．对Northwind数据库完成一下查询
USE Northwind
--1、查询每个订单购买产品的数量和总金额，显示订单号，数量，总金额
select orders.OrderID,数量 = sum([Order Details].Quantity),总金额 = sum(UnitPrice*(1-Discount)*Quantity)
from orders,[Order Details]
where orders.OrderID = [Order Details].OrderID
group by orders.OrderID

--2、查询每个员工在7月份处理订单的数量
select Orders.EmployeeID ,订单数 = count(*)
from Orders
where MONTH(OrderDate) = 7
group by Orders.EmployeeID

--3、查询每个顾客的订单总数，显示顾客ID，订单总数
select Customers.CustomerID,订单总数 = count(orders.OrderID)
from Customers left join orders on Customers.CustomerID = orders.CustomerID
group by Customers.CustomerID

--4、 查询每个顾客的订单总数和订单总金额
select Customers.CustomerID,订单总数 = count(distinct orders.OrderID),订单总金额 = sum(UnitPrice*(1-Discount)*Quantity)
from Customers left join orders on Customers.CustomerID = orders.CustomerID
               left join [Order Details] on orders.OrderID = [Order Details].OrderID
group by Customers.CustomerID
order by customers.CustomerID asc

--5、 查询每种产品的卖出总数和总金额
select products.ProductID , 总数 = sum(Quantity), 总金额 = sum([Order Details].UnitPrice*(1-Discount)*Quantity)
from Products left join [Order Details] on products.ProductID = [Order Details].ProductID
group by products.ProductID
